<?php
/**
 * File: Model.php
 * Functionality: Core PDO model class
 * Author: 大眼猫
 * Date: 2013-2-28
 * Note:
 *	1 => This class requires PDO support !
 *	2 => $conn MUST BE set to static for transaction !
 */

abstract class Model {

    private static $obj;
    private static $conn;
    private $result = NULL;				
    protected $table;				
    private $options;            // SQL 中的 field, where, orderby, limit
    private $selectOne = FALSE;  // 是否是 SelectOne, 不需要 updateOne, deleteOne

    // success code of PDO
    private $successCode = '00000';

    // The result of last operation: failure OR success
    private $success = FALSE;

    // SQL log file: Log SQL error for debug if NOT under DEV
    private $logFile;

    public $bind_marker		= '?';
    protected $_like_escape_str = " ESCAPE '%s' ";
    protected $_like_escape_chr = '!';
    
    private static $params = NULL;


    /**
     * Constructor
     */
    function __construct($params = []) {
        if($params){
            $this->params = $params;
        }
        
        $this->logFile = APP_PATH. '/log/sql/'.CUR_DATE.'.log';
    }

    /**
     * Connect to MySQL [Support read/write splitting]
     *
     * @param string => use default DB if parameter is not specified !
     * @return NULL
     */
    private function connect($type = 'WRITE') {
        $config = Yaf_Application::app()->getConfig();
        
        if($this->params){
            $db     = $this->params['db'];
            $driver = $this->params['driver'];
            $host   = $this->params['host'];
            $port   = $this->params['port'];
            $user   = $this->params['user'];
            $pswd   = $this->params['pwd'];
            $persistent = $this->params['pconnect'];
            
            // 判断 READ, WRITE 是否是相同的配置, 是则用同一个链接, 不再创建连接
            $read_host = $this->params['host'];
            $read_port = $this->params['port'];

            $write_host = $this->params['host'];
            $write_port = $this->params['port'];
        }
        else{
            $db     = $config['Default'];
            $driver = $config['TYPE'];
            $host   = $config[$type.'_HOST'];
            $port   = $config[$type.'_PORT'];
            $user   = $config[$type.'_USER'];
            $pswd   = $config[$type.'_PSWD'];
            $persistent = $config['pconnect'];
            
            // 判断 READ, WRITE 是否是相同的配置, 是则用同一个链接, 不再创建连接
            $read_host = $config['READ_HOST'];
            $read_port = $config['READ_PORT'];

            $write_host = $config['WRITE_HOST'];
            $write_port = $config['WRITE_PORT'];
        }
        
        if($persistent){
            $option = array(PDO::ATTR_PERSISTENT => 1);
        }
        else{
            $option = array();
        }

        if(!$port){
            $port = 3306;
        }

        $dsn = $driver.':host='.$host.';port='.$port.';dbname='.$db;
        
        try{
            //判断一下当前访问的数据库是否一致，如果一致的话则为相同链接
            $sington = false;
            
            if($read_host == $write_host && $read_port == $write_port){
                $sington = true;
            }

            if($sington){
                if(isset(self::$obj)){
                    
                    if(isset(self::$obj['READ'.$dsn])) {
                        self::$obj['WRITE'.$dsn] = self::$obj['READ'.$dsn];
                    }
                    else{
                        self::$obj['READ'.$dsn] = self::$obj['WRITE'.$dsn];
                    }

                    self::$conn = self::$obj['WRITE'.$dsn];
                }
            }

            // 读写要分离则创建两个连接
            if(!isset(self::$obj[$type.$dsn])) {
                self::$conn = self::$obj[$type.$dsn] = new PDO($dsn, $user, $pswd, $option);
                self::$conn->query('SET NAMES utf8');
                unset($db, $driver, $host, $port, $user, $pswd, $dsn);
            }
        }
        catch(PDOException $e){
            if(ENV == 'DEV'){
                Helper::raiseError(debug_backtrace(), $e->getMessage());
            }
            else{
                file_put_contents($this->logFile, $e->getMessage().PHP_EOL, FILE_APPEND);
            }
        }
    }

    /**
     * Field
     */
    final public function Field($field){
        if(!$field){
            return $this;
        }

        $str = '';
        if(is_array($field)){
            foreach($field as $val){
                // 判断有没有 AS
                if(strpos($val, strtoupper('as')) !== FALSE){
                    $str .= $val.',';
                }
                else{
                    $str .= '`'.$val.'`, ';
                }
            }

            $this->options['field'] = substr($str, 0, strlen($str)-2); // 2:　Cos there is a BLANK
        }
        else{
            $this->options['field'] = $field;
        }

        unset($str, $field);
        return $this;
    }

    /**
     * Between 支持多次调用
     */
    final public function Between($key, $start, $end){
        $str = '`'.$key.'` BETWEEN "'.$start.'" AND "'.$end.'"';
        if(isset($this->options['between'])){
            $this->options['between'] .= ' AND '.$str;
        }
        else{
            $this->options['between'] = $str;
        }

        return $this;
    }

    /**
     * OR 也支持多次调用
     * 因为 OR 为PHP 关键字, 不能用 OR 作函数名了
     */
    final public function ORR(){
        $this->options['or'] = TRUE;

        return $this;
    }

    /**
     * sql 参数绑定
     */
    public function CompileBinds($sql, $binds = []){
        if(empty($binds) OR empty($this->bind_marker) OR strpos($sql, $this->bind_marker) === FALSE){
            $this->sql = $sql;
            return $this;
        }
        elseif(!is_array($binds)){
            $binds = array($binds);
            $bind_count = 1;
        }
        else{
            // Make sure we're using numeric keys
            $binds = array_values($binds);
            $bind_count = count($binds);
        }

        // We'll need the marker length later
        $ml = strlen($this->bind_marker);

        // Make sure not to replace a chunk inside a string that happens to match the bind marker
        if($c = preg_match_all("/'[^']*'/i", $sql, $matches)){
            $c = preg_match_all('/'.preg_quote($this->bind_marker, '/').'/i',
                    str_replace($matches[0],
                    str_replace($this->bind_marker, str_repeat(' ', $ml), $matches[0]),
                    $sql, $c),
                    $matches, PREG_OFFSET_CAPTURE);

            // Bind values' count must match the count of markers in the query
            if ($bind_count !== $c){
                $this->sql = $sql;
                return $this;
            }
        }
        elseif (($c = preg_match_all('/'.preg_quote($this->bind_marker, '/').'/i', $sql, $matches, PREG_OFFSET_CAPTURE)) !== $bind_count){
            $this->sql = $sql;
            return $this;
        }

        do{
            $c--;
            $escaped_value = $this->escape($binds[$c]);
            if (is_array($escaped_value)){
                $escaped_value = '('.implode(',', $escaped_value).')';
            }
            $sql = substr_replace($sql, $escaped_value, $matches[0][$c][1], $ml);
        }
        while ($c !== 0);

        $this->sql = $sql;
        return $this;
    }

    public function escape($str){
        if (is_array($str)){
            $str = array_map(array(&$this, 'escape'), $str);
            return $str;
        }
        elseif(is_numeric($str) && strlen($str) <= 9){
            return floatval($str);
        }
        elseif(is_string($str) OR (is_object($str) && method_exists($str, '__toString'))){
            return "'".$this->escape_str($this->html_filter($str))."'";
        }
        elseif(is_bool($str)){
            return ($str === FALSE) ? 0 : 1;
        }
        elseif($str === NULL){
            return 'NULL';
        }

        return $str;
    }
    
    public function html_filter($str){
        return str_replace(array("<", ">"), array("&lt;", "&gt;"), $str);
    }

    public function unhtml_filter($str){
        return str_replace(array("&lt;", "&gt;"), array("<", ">"), $str);
    }
    
    public function escape_str($str, $like = FALSE){
        if (is_array($str)){
            foreach ($str as $key => $val){
                $str[$key] = $this->escape_str($val, $like);
            }

            return $str;
        }

        $str = $this->_escape_str($str);

        // escape LIKE condition wildcards
        if($like === TRUE){
            return str_replace(
                array($this->_like_escape_chr, '%', '_'),
                array($this->_like_escape_chr.$this->_like_escape_chr, $this->_like_escape_chr.'%', $this->_like_escape_chr.'_'),
                $str
            );
        }

        return $str;
    }
    
    protected function _escape_str($str){
        return str_replace("'", "''", $this->remove_invisible_characters($str));
    }
    
    public function remove_invisible_characters($str, $url_encoded = TRUE){
        $non_displayables = array();

        // every control character except newline (dec 10),
        // carriage return (dec 13) and horizontal tab (dec 09)
        if ($url_encoded){
            $non_displayables[] = '/%0[0-8bcef]/';	// url encoded 00-08, 11, 12, 14, 15
            $non_displayables[] = '/%1[0-9a-f]/';	// url encoded 16-31
        }

        $non_displayables[] = '/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]+/S';	// 00-08, 11, 12, 14-31, 127

        do{
            $str = preg_replace($non_displayables, '', $str, -1, $count);
        }
        while($count);

        return $str;
    }
    
    /**
     * Where 支持多次调用
     * where 有三种调用方式
     */
    final public function Where($where, $condition = '', $value = ''){
        if(!$where){
            return $this;
        }

        if(is_array($where)){
            // 1: $where = array('username' => 'yaf'); 这样的形式
            $total = sizeof($where);
            $i   = 1;
            $str = '';
            foreach($where as $key => $val){
                $str .= '`'.$key.'` = "'.$val.'"';
                if($i != $total){
                    $str .= ' AND ';
                }
                $i++;
            }
        }
        else{
            // 2: $this->Where($where, $condition, $val); 这样的形式
            // $condition 可为 =, !=, >, >=, <, <=, IN, NOT IN, LIKE, NOT LIKE
            if($condition){
                // 此时的 $where 变成了表字段
                $str .= ' `'.$where.'`'.' '.$condition.' ';

                // 是否是 IN, NOT IN, 是则值带上 (), 支持数组或字符串
                if(stripos($condition, 'IN') !== FALSE){
                    // 如果是数组, 则 implode
                    if(is_array($value)){
                        $str .= '(';
                        foreach($value as $v){
                            $str .= '"'.$v.'",';
                        }

                        // 去掉,
                        $str = substr($str, 0, -1);
                        $str .= ')';
                    }
                    else{
                        $error = 'The value of IN MUST BE an array';
                        $trace = debug_backtrace();
                        Helper::raiseError($trace, $error);
                    }
                }
                elseif(stripos($condition, 'LIKE') !== FALSE){
                    // 是否是 LIKE, NOT LIKE
                    $str .= '"%'.$value.'%"';
                }
                else{
                    // =, !=, >, >=, <, <= 等形式
                    $str .= '"'.$value.'"';
                }
            }
            else{
                // 3: $where = 'username != "yaf"'; 这样的字符串形式
                $str = $where;
            }
        }

        // 无限 WHERE
        if(isset($this->options['where'])){
                // 是否是 OR
                if($this->options['or']){
                        $connector = ' OR ';
                        $this->options['or'] = FALSE;
                }else{
                        $connector = ' AND ';
                }

                $this->options['where'] .= $connector.$str;
        }else{
                $this->options['where'] = $str;
        }

        unset($str, $i, $total, $where, $connector);

        return $this;
    }


    /*
     * Order 支持多次调用
     */
    final public function Order($order){
        if(!$order){
            return $this;
        }

        if(is_array($order)){
            $total = sizeof($order);
            $i   = 1;
            $str = '';
            foreach($order as $key => $val){
                $str .= '`'.$key.'` '.$val;
                if($i != $total){
                    $str .= ' , ';
                }
                $i++;
            }
        }
        else{
            $str = $order;
        }

        if(isset($this->options['order'])){
            $this->options['order'] .= ', '.$str;
        }
        else{
            $this->options['order'] = $str;
        }

        unset($str, $i, $total, $order);

        return $this;
    }

    /*
     * Limit
     * 可传一个或二个参数
     */
    final public function Limit($start, $size = ''){
        $this->options['limit'] = $start;

        if($size){
            $this->options['limit'] .= ', '.$size;
        }

        unset($start, $size);

        return $this;
    }

    // Reset SQL options
    final private function _reset() {
        unset($this->options);
    }


    /**
     * Select records
     * @return records on success or FALSE on failure 
     */
    final public function Select(){
        if(!$this->sql){
            $this->sql = $this->generateSQL();
        }
        
        //判断一下 $this->sql 当前的语句是否是select语句，如果不是的话还是要调用 generateSQL() 方法的
        if(strtoupper(substr(trim($this->sql), 0, 6)) != 'SELECT'){
            $this->sql = $this->generateSQL();
        }
        
//        echo $this->sql; br();

        // 连接DB
        $this->connect('READ');

        $this->Execute();
        $result = $this->success ? $this->Fetch() : NULL;

        if($this->selectOne == TRUE){
            $data = $result[0];
        }
        else{
            $data = $result;
        }

        $this->sql = '';
        $this->selectOne = FALSE;
        return $data;
    }


    /**
     * Select one record
     */
    final public function SelectOne(){
        $this->options['limit'] = 1;
        $this->selectOne = TRUE;

        return $this->Select();
    }


    /**
     * Insert | Add a new record
     *
     * @param Array => Array('field1'=>'value1', 'field2'=>'value2', 'field3'=>'value1')
     * @return FALSE on failure or inserted_id on success
     */
    final public function Insert($map = array()) {
        if(!$map || !is_array($map)) {
            return FALSE;
        }
        else{
            $fields = $values = array();

            foreach ($map as $key => $value) {
                $fields[] = '`' . $key . '`';
                $values[] = "'$value'";
            }

            $fieldString = implode(',', $fields);
            $valueString = implode(',', $values);

            $this->sql = 'INSERT INTO ' . $this->table . " ($fieldString) VALUES ($valueString)";
            
            $this->connect();
            $this->Execute();

            return $this->success ? $this->getInsertID() : NULL;
        }
    }


    /**
     * Insert | Add a list record
     *
     * @param type $data
     * @return boolean
     */
    public function MultiInsert($data){
        $sql = "INSERT INTO ". $this->table;
        $sqlFieldArr = array();
        $sqlValueArr = array();
        $first = TRUE;

        foreach($data as $item){
            if(!is_array($item)){
                return FALSE;
            }

            if($first){
                $sqlFieldArr = array_keys($item);

                $sqlFieldStr = implode('`,`', $sqlFieldArr);
                $first = FALSE;
            }

            $tmp = implode('\',\'', $item);
            $tmp = "('$tmp')";
            $sqlValueArr[] = $tmp;
        }

        $sqlValueStr = implode(',', $sqlValueArr);
        $sql .= "(`$sqlFieldStr`) VALUES $sqlValueStr";

        $this->sql = $sql;
        $this->Execute();

        return $this->success ? $this->getInsertID() : NULL;
    }

    /**
     * Replace | Add a new record if not exit, update if exits;
     *
     * @param Array => Array('field1'=>'value1', 'field2'=>'value2', 'field3'=>'value1')
     * @return FALSE on failure or inserted_id on success
     */
    final public function ReplaceInto($map) {
        if(!$map || !is_array($map)) {
            return FALSE;
        }
        else{
            $fields = $values = array();

            foreach ($map as $key => $value) {
                $fields[] = '`' . $key . '`';
                $values[] = "'$value'";
            }

            $fieldString = implode(',', $fields);
            $valueString = implode(',', $values);

            $sql = 'REPLACE INTO ' . $this->table . " ($fieldString) VALUES ($valueString)";
            $this->sql = $sql;

            $this->Execute();

            return $this->success ? $this->getInsertID() : NULL;
        }
    }


    /**
     * Execute special SELECT SQL statement
     *
     * @param string  => SQL statement for execution
     */
    final public function Query($sql) {
        if($sql){
            $this->sql = $sql;
        }
        else{
            return NULL;
        }

        $this->connect();
        $this->Execute();
        $this->checkResult();

        if($this->success){
            return $this->Fetch();
        }
        else{
            return FALSE;
        }
    }

    final public function QueryOnly() {
        $this->connect();
        $this->Execute();
        $this->checkResult();

        if($this->success){
            return $this->Fetch();
        }
        else{
            return FALSE;
        }
    }
    
    // 根据ID 查询字段:
    public function SelectByID($field, $id){
        $where = array(TB_PK => $id);
        return $this->Field($field)->Where($where)->SelectOne();
    }

    // 根据ID更新某一条记录
    public function UpdateByID($map, $id){
        $where = array(TB_PK => $id);
        return $this->Where($where)->UpdateOne($map);
    }

    // 根据ID删除某一条记录
    public function DeleteByID($id){
        if(!$id || !is_numeric($id)){
            return FALSE;
        }

        $where = array(TB_PK => $id);
        return $this->Where($where)->DeleteOne();
    }

    // 根据ID获取某个字段
    public function SelectFieldByID($field, $id){
        $where = array(TB_PK => $id);
        $data = $this->Field($field)->Where($where)->SelectOne();
        return $data[$field];
    }

    /**
     * Generate SQL by options for Select, SelectOne
     */
    final protected function generateSQL(){
        if(isset($this->options['field'])){
            $field = $this->options['field'];
        }
        else{
            $field = '*';
        }

        $sql = 'SELECT '. $field .' FROM `'. $this->table. '`';

        if(isset($this->options['where'])){
            $sql .= ' WHERE '. $this->options['where'];
        }

        // 是否有 BETWEEN
        if(isset($this->options['between'])){
            if(isset($this->options['where'])){
                $sql .= ' AND ';
            }
            else{
                $sql .= ' WHERE ';
            }

            $sql .= $this->options['between'];
        }

        if(isset($this->options['order'])){
            $sql .= ' ORDER BY '. $this->options['order'];
        }

        if(isset($this->options['limit'])){
            $sql .= ' LIMIT '. $this->options['limit'];
        }

        //echo $sql; br();
        return $sql;
    }


    /**
     * Return last inserted_id
     *
     * @param NULL
     * @return the last inserted_id
     */
    public function getInsertID() {
        return self::$conn->lastInsertId();
    }


    /**
     * Fetch data
     */
    private function Fetch() {
        return $this->result->fetchAll(PDO::FETCH_ASSOC);
    }


    /**
     * Calculate record counts
     *
     * @param string => where condition
     * @return int => total record counts
     */
    final public function Total() {
        $data = $this->Field('COUNT(*) AS `total`')->SelectOne();
        return $data['total'];
    }


    /**
     * Execute SELECT | INSERT SQL statements
     *
     * <br /> Remark:  If error occurs and UAT is TRUE, call raiseError() to display error and halt !
     * @param string => SQL statement to execute
     * @return result of execution
     */
    final private function Execute() {
//        P(self::$conn);
        $this->result = self::$conn->query($this->sql);
        $this->checkResult();
    }


    /**
     * Update record(s)
     *
     * @param array  => $map = array('field1'=>value1, 'field2'=>value2, 'field3'=>value3))
     * @param boolean $self => self field ?
     * @return FALSE on failure or affected rows on success
     */
    final public function Update($map, $self = FALSE) {
        if(!$this->options['where'] && !$this->options['between']){
            return FALSE;
        }

        if(!$map){
            return FALSE;
        }
        else{
            $this->sql = 'UPDATE `' . $this->table .'` SET ';
            $sets = array();
            if($self){
                foreach ($map as $key => $value) {
                    if(strpos($value, '+') !== FALSE) {
                        list($flag, $v) = explode('+', $value);
                        $sets[] = "`$key` = `$key` + '$v'";
                    }
                    elseif (strpos($value, '-') !== FALSE) {
                        list($flag, $v) = explode('-', $value);
                        $sets[] = "`$key` = `$key` - '$v'";
                    }else {
                        $sets[] = "`$key` = '$value'";
                    }
                }
            }
            else{
                foreach ($map as $key => $value) {
                    $sets[] = "`$key` = '$value'";
                }
            }

            $this->sql .= implode(',', $sets). ' ';

            if(isset($this->options['where'])){
                $this->sql .= ' WHERE '.$this->options['where'];
            }

            // 是否有 BETWEEN
            if(isset($this->options['between'])){
                if(isset($this->options['where'])){
                    $this->sql .= ' AND ';
                }
                else{
                    $this->sql .= ' WHERE ';
                }

                $this->sql .= $this->options['between'];
            }

            if(isset($this->options['order'])){
                $this->sql .= ' ORDER BY '. $this->options['order'];
            }

            if(isset($this->options['limit'])){
                $this->sql .= ' LIMIT '.$this->options['limit'];
            }

            // echo $this->sql; die;
            $this->connect();

            return $this->Exec();
        }
    }

    /*
    *  Update one record
    */
    public function UpdateOne($map, $self = FALSE){
        $this->options['limit'] = 1;
        return $this->Update($map, $self);
    }


    /**
     * Delete record(s)
     * @param string => where condition for deletion
     * @return FALSE on failure or affected rows on success
     */
    final public function Delete() {
        if(!$this->options['where'] && !$this->options['between']){
            return FALSE;
        }

        $this->sql = 'DELETE FROM `'.$this->table.'` WHERE '.$this->options['where'];

        // 是否有 BETWEEN
        if(isset($this->options['between'])){
            if(isset($this->options['where'])){
                    $this->sql .= ' AND ';
            }
            else{
                    $this->sql .= ' ';
            }

            $this->sql .= $this->options['between'];
        }

        if(isset($this->options['order'])){
            $this->sql .= ' ORDER BY '. $this->options['order'];
        }

        if(isset($this->options['limit'])){
            $this->sql .= ' LIMIT '.$this->options['limit'];
        }

        //echo $this->sql; die;
        $this->connect();
        return $this->Exec();
    }

    /**
     * Delete record(s)
     * @param string => where condition for deletion
     * @return FALSE on failure or affected rows on success
     */
    final public function DeleteOne() {
        $this->options['limit'] = 1;
        return $this->Delete();
    }

    /**
     * Execute UPDATE, DELETE SQL statements
     * <br />Remark:  If error occurs and UAT is TRUE, call raiseError() to display error and halt !
     *
     * @return result of execution
     */
    final private function Exec() {
        $rows = self::$conn->exec($this->sql);
        $this->checkResult();

        return $rows;
    }

    private function getUnderscore($total = 10, $sub = 0) {
        $result = '';
        for($i=$sub; $i<= $total; $i++){
            $result .= '_';
        }
        return $result;
    }

    /**
     * Check result for the last execution
     *
     * @param NULL
     * @return NULL
     */
    final private function checkResult() {
        $this->_reset();

        if (self::$conn->errorCode() != $this->successCode) {
            $this->success = FALSE;
            $error = self::$conn->errorInfo();
            $traceInfo = debug_backtrace();

            if (ENV == 'DEV') {
                Helper::raiseError($traceInfo, $error[2], $this->sql);
            }
            else{
                // Log error SQL and reason for debug
                $errorMsg = getClientIP(). ' | ' .date('Y-m-d H:i:s') .PHP_EOL;
                $errorMsg .= 'SQL: '. $this->sql .PHP_EOL;
                $errorMsg .= 'Error: '.$error[2]. PHP_EOL;

                $title =  'LINE__________FUNCTION__________FILE______________________________________'.PHP_EOL;
                $errorMsg .= $title;

                foreach ($traceInfo as $v) {
                    $errorMsg .= $v['line'];
                    $errorMsg .= $this->getUnderscore(10, strlen($v['line']));
                    $errorMsg .= $v['function'];
                    $errorMsg .= $this->getUnderscore(20, strlen($v['function']));
                    $errorMsg .= $v['file'].PHP_EOL;
                }

                file_put_contents($this->logFile, PHP_EOL.$errorMsg, FILE_APPEND);

                return FALSE;
            }
        }
        else{
            $this->success = TRUE;
        }
    }

    // ********* Execute transaction ********* //
    /**
     * Start a transaction
     *
     * @param NULL
     * @return TRUE on success or FALSE on failure
     */
    public function beginTransaction() {
        $this->connect();
        self::$conn->beginTransaction();
    }


    /**
     * Commit a transaction
     *
     * @param NULL
     * @return TRUE on success or FALSE on failure
     */
    public function Commit() {
        self::$conn->commit();
    }


    /**
     * Rollback a transaction
     *
     * @param  NULL
     * @return TRUE on success or FALSE on failure
     */
    public function Rollback() {
        self::$conn->rollBack();
    }
    // *************** End ***************** //

    /**
     * Close connection
     *
     * @param NULL
     * @return NULL
     */
    private function Close() {
        self::$conn = NULL;
    }

    /**
     * Destructor
     *
     * @param NULL
     * @return NULL
     */
    function __destruct() {
        $this->Close();
    }

}